/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package org.dusin.adserver.db.daoimpl4mysql;

import java.util.Arrays;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.dusin.adserver.db.dao.GerneralDao;
import org.dusin.adserver.utils.SqlUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;

/**
 *
 * @author dusin
 */
public class GerneralDaoImpl implements GerneralDao {

    static Logger logger = Logger.getLogger(GerneralDaoImpl.class);
    private JdbcTemplate jdbcTemplate;

    public GerneralDaoImpl(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public List<Map<String,Object>> findSum4GP3Each(String dateFrom,String dateTo,String[] models,String[] companeis){
        StringBuilder sql = new StringBuilder();
        sql.append("select round(sum(gp3)/sum(salesVolumn),2) value ,code,name from profit left join org on profit.orgCode = org.code where yearmonth>=? and yearmonth<=? ");
        sql.append(SqlUtils.join("model", models));
        sql.append(" group by code");       
        logger.info(sql.toString() + Arrays.asList(new Object[]{dateFrom, dateTo}));
        List<Map<String, Object>> list = null;
        list = jdbcTemplate.queryForList(sql.toString(), dateFrom, dateTo);
        return list;
    }
    
    //单台毛利
    public List<Map<String,Object>> findSum4MLEach(String dateFrom,String dateTo,String[] models,String[] companeis){
        StringBuilder sql = new StringBuilder();
        sql.append("select round(sum(gp3-cost1-cost2-cost3-cost4)/sum(salesVolumn),2) value ,code,name from profit left join org on profit.orgCode = org.code where yearmonth>=? and yearmonth<=? ");
        sql.append(SqlUtils.join("model", models));
        sql.append(" group by code");       
        logger.info(sql.toString() + Arrays.asList(new Object[]{dateFrom, dateTo}));
        List<Map<String, Object>> list = null;
        list = jdbcTemplate.queryForList(sql.toString(), dateFrom, dateTo);
        return list;
    }
    
    @Override
    public List<Map<String, Object>> findAllProfit(String dateFrom, String dateTo, String[] models, String[] dqs) {
        String sql = "select round(avg(salesVolumn),2) value ,code,name,yearMonth from profit left join org on profit.orgCode = org.code where yearmonth>=? and yearmonth<=? group by yearmonth, code";
        //String sql = "select * from profit";
        logger.info(sql + Arrays.asList(new Object[]{dateFrom, dateTo}));
        List<Map<String, Object>> list = null;
        list = jdbcTemplate.queryForList(sql, dateFrom, dateTo);
        return list;
    }

    /**
     * 统计三店销量占比，用于饼图
     * @param dateFrom
     * @param dateTo
     * @param models  ,统计车型
     * @param dqs, 忽略
     * @return 
     */
    public List<Map<String, Object>> findAllProfitSum4ModelPie(String dateFrom, String dateTo, String[] models, String[] dqs) {
        String sql = "select round(sum(salesVolumn),2) value ,code,name from profit left join org on profit.orgCode = org.code "
                + "where yearmonth>=? and yearmonth<=?  "+SqlUtils.join("model", models)+" group by  code";
        //String sql = "select * from profit";
        logger.info(sql + Arrays.asList(new Object[]{dateFrom, dateTo,models,dqs}));
        List<Map<String, Object>> list = null;
        list = jdbcTemplate.queryForList(sql, dateFrom, dateTo);
        return list;
    }
    
    /**
     * 流向统计
     * @param dateFrom
     * @param dateTo
     * @param models
     * @param dqs
     * @return 
     */
    public List<Map<String, Object>> findAllProfitSum4Where(String dateFrom, String dateTo, String[] models, String[] dqs) {
        String sql = "select round(sum(salesVolumn),2) value ,code,name from profit left join org on profit.orgCode = org.code "
                + "where yearmonth>=? and yearmonth<=?  "+SqlUtils.join("model", models)+" group by  code";
        //String sql = "select * from profit";
        logger.info(sql + Arrays.asList(new Object[]{dateFrom, dateTo,models,dqs}));
        List<Map<String, Object>> list = null;
        list = jdbcTemplate.queryForList(sql, dateFrom, dateTo);
        return list;
    }

    
    @Override
    public List<Map<String, Object>> findSum4GoWhere(String dateFrom, String dateTo, String[] models, String[] companies) {
        String sql = "select sum(zjsq) zjsq, sum(dy) dy,sum(yz) yz, sum(jr) jr, sum(sn) sn,sum(sw) sw from profit  "
                + "where yearmonth>=? and yearmonth<=?  "+SqlUtils.join("model", models)
                +SqlUtils.join("orgCode", companies);
        //String sql = "select * from profit";
        logger.info(sql + Arrays.asList(new Object[]{dateFrom, dateTo,models,companies}));
        List<Map<String, Object>> list = null;
        list = jdbcTemplate.queryForList(sql, dateFrom, dateTo);
        return list;
    }
    
    public List<Map<String,Object>> findAllClzb(){
        String sql = "select * from clzb order by type,zbid";
        //String sql = "select * from profit";
        logger.info(sql );
        List<Map<String, Object>> list = null;
        list = jdbcTemplate.queryForList(sql);
        return list;
    }

    @Override
    public List<Map<String, Object>> findAll4GP3EachTrend(String dateFrom, String dateTo, String[] models, String[] companeis) {
         String sql = "select round(sum(gp3)/sum(salesVolumn),2) value ,orgCode,yearMonth from profit  "
                + "where yearmonth>=? and yearmonth<=?  "+SqlUtils.join("model", models)+" group by yearmonth,orgcode order by yearmonth,orgCode";
        //String sql = "select * from profit";
        logger.info(sql + Arrays.asList(new Object[]{dateFrom, dateTo,models,companeis}));
        List<Map<String, Object>> list = null;
        list = jdbcTemplate.queryForList(sql, dateFrom, dateTo);
        return list;
    }

    @Override
    public List<Map<String, Object>> findAll4MaoliEachTrend(String dateFrom, String dateTo, String[] models, String[] companeis) {
         String sql = "select round(sum(gp3-cost1-cost2-cost3-cost4)/sum(salesVolumn),2) value ,orgCode,yearMonth from profit  "
                + "where yearmonth>=? and yearmonth<=?  "+SqlUtils.join("model", models)+" group by yearmonth,orgcode order by yearmonth,orgCode";
        //String sql = "select * from profit";
        logger.info(sql + Arrays.asList(new Object[]{dateFrom, dateTo,models,companeis}));
        List<Map<String, Object>> list = null;
        list = jdbcTemplate.queryForList(sql, dateFrom, dateTo);
        return list;
    }
        @Override
    public List<Map<String, Object>> findAll4VolumeTrend(String dateFrom, String dateTo, String[] models, String[] companeis) {
         String sql = "select sum(salesVolumn) value ,orgCode,yearMonth from profit  "
                + "where yearmonth>=? and yearmonth<=?  "+SqlUtils.join("model", models)+" group by yearmonth,orgCode order by yearmonth,orgCode";
        
        logger.info(sql + Arrays.asList(new Object[]{dateFrom, dateTo,models,companeis}));
        List<Map<String, Object>> list = null;
        list = jdbcTemplate.queryForList(sql, dateFrom, dateTo);
        return list;
    }
 
}
